package com.atguigu.gmall.publisher.mapper;

import com.atguigu.gmall.publisher.beans.ProductStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * 商品统计Mapper接口
 *
 * @author lvbingbing
 * @date 2022-07-07 11:13
 */
public interface ProductStatsMapper {

    /**
     * 获取某天商品的总交易额
     *
     * @param date <br>
     * @return <br>
     */
    @Select(" select " +
            " sum(order_amount) as order_amount " +
            " from product_stats_2021 " +
            " where toYYYYMMDD(stt) = #{date}")
    BigDecimal selectGmv(Integer date);

    @Select(" select " +
            " tm_id, " +
            " tm_name, " +
            " sum(order_amount) as order_amount " +
            " from product_stats_2021 " +
            " where toYYYYMMDD(stt) = #{date} " +
            " group by tm_id, tm_name " +
            " having order_amount > 0 " +
            " order by order_amount desc " +
            " limit #{limit} ")
    List<ProductStats> selectProductStatsByTm(@Param("date") Integer date, @Param("limit") Integer limit);

    @Select(" select " +
            " category3_id, " +
            " category3_name, " +
            " sum(order_amount) as order_amount " +
            " from product_stats_2021 " +
            " where toYYYYMMDD(stt) = #{date} " +
            " group by category3_id, category3_name " +
            " having order_amount > 0 " +
            " order by order_amount desc " +
            " limit #{limit} ")
    List<ProductStats> selectProductStatsByCategory3(@Param("date") Integer date, @Param("limit") Integer limit);


    @Select(" select " +
            " spu_id, " +
            " spu_name, " +
            " sum(order_amount) as order_amount, " +
            " sum(order_ct) as order_ct " +
            " from product_stats_2021 " +
            " where toYYYYMMDD(stt) = #{date} " +
            " group by spu_id, spu_name " +
            " having order_amount > 0 " +
            " order by order_amount desc " +
            " limit #{limit} ")
    List<ProductStats> selectProductStatsBySpu(@Param("date") Integer date, @Param("limit") Integer limit);
}